﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace HRdex_Final.Forms
{
    public partial class WebForm6 : System.Web.UI.Page
    {
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlCommand cmd22 = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name, DeptID, catPositionsID, CollegeID FROM formEmployees WHERE (EmployeeID =" + SessionID() + ")", conn);
            SqlDataAdapter da22 = new SqlDataAdapter(cmd22);
            DataTable dt22 = new DataTable("dt22");

            da22.Fill(dt22);

            txtEmpNo.Text = dt22.Rows[0].ItemArray[0].ToString();
            txtEmpName.Text = dt22.Rows[0].ItemArray[1].ToString();
            ddlPositions.SelectedValue = dt22.Rows[0].ItemArray[3].ToString();
            ddlOffice.SelectedValue = dt22.Rows[0].ItemArray[4].ToString();

            Selected();

            txtfDate.Visible = true;
            txtfTime.Visible = true;
            txtODate.Visible = true;
            txtOTime.Visible = false;
            ddlPositions.Enabled = false;
            ddlOffice.Enabled = false;
            txtEmpName.Enabled = false;
            txtEmpNo.Enabled = false;
            lblConfirmationMessage.Visible = false;
            ddlApprovalHead.Visible = false;
            ddlApprovalDean.Visible = false;
            ddlApprovalHR.Visible = false;
           
        }
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            conn.Open();

            SqlCommand cmd = new SqlCommand("Insert Into formFlexi (EmpID, CollegeID, PositionID, "+
                "originalDate, originalTime, ActivityDesc, FlexiDate, FlexiTime, PubDate)"+
                "Values (@empID, @collegeID, @posID, @oDate, @oTime, @actDesc, @fDate,"+
                "@fTime, @pubDate)", conn);

            cmd.Parameters.Add("@empID", SqlDbType.Int).Value = txtEmpNo.Text;
            cmd.Parameters.Add("@collegeID", SqlDbType.Int).Value = ddlOffice.SelectedValue;
            cmd.Parameters.Add("@posID", SqlDbType.Int).Value = ddlPositions.SelectedValue;
            cmd.Parameters.Add("@oDate", SqlDbType.VarChar).Value = txtODate.Text;
            cmd.Parameters.Add("@oTime", SqlDbType.VarChar).Value = ddlOhr.Text + ":" + ddlOmin.Text + "-" + ddlOhr0.Text + ":" + ddlOmin.Text;
            cmd.Parameters.Add("@actDesc", SqlDbType.VarChar).Value = txtActivityReason.Text;
            cmd.Parameters.Add("@fDate", SqlDbType.VarChar).Value = txtfDate.Text;
            cmd.Parameters.Add("@fTime", SqlDbType.VarChar).Value = ddlFlexiHour.Text + ":" + ddlFlexiMin.Text + "-" + ddlFlexihr0.Text + ":" + ddlFleximin1.Text;
            cmd.Parameters.Add("@pubDate", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.ExecuteNonQuery();

            conn.Close();
            lblConfirmationMessage.Visible = true;
            lblConfirmationMessage.Text = "You successfully request a flexi time. Please wait for the confirmation upon reviewing your schedule.";
        }
        public void Selected()
        {
            string FlexiID = Request.QueryString["FlexiID"];
            if (FlexiID != null)
            {
                ddlOhr.Visible = false;
                ddlOmin.Visible = false;
                ddlFlexiHour.Visible = false;
                ddlFlexiMin.Visible = false;

                SqlCommand cmd2 = new SqlCommand("SELECT formFlexi.FlexiID, formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, " +
                    "formEmployees.EmployeeID, formFlexi.PositionID, formFlexi.CollegeID, " +
                    "formFlexi.FlexiDate, formFlexi.FlexiTime, formFlexi.ActivityDesc, formFlexi.OriginalDate, " +
                    "formFlexi.OriginalTime, formFlexi.pubDate FROM formFlexi INNER JOIN formEmployees ON " +
                    "formFlexi.EmpID = formEmployees.EmployeeID AND formFlexi.EmpID = formEmployees.EmployeeID " +
                    "WHERE (FlexiID = " + FlexiID + ")", conn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable dt2 = new DataTable("dt2");

                da2.Fill(dt2);
                txtEmpNo.Text = dt2.Rows[0].ItemArray[2].ToString();
                txtEmpName.Text = dt2.Rows[0].ItemArray[1].ToString();
                ddlPositions.SelectedValue = dt2.Rows[0].ItemArray[3].ToString();
                ddlOffice.SelectedValue = dt2.Rows[0].ItemArray[4].ToString();
                txtfDate.Text = dt2.Rows[0].ItemArray[5].ToString();
                txtfTime.Text = dt2.Rows[0].ItemArray[6].ToString();
                txtActivityReason.Text = dt2.Rows[0].ItemArray[7].ToString();
                txtODate.Text = dt2.Rows[0].ItemArray[8].ToString();
                txtOTime.Text = dt2.Rows[0].ItemArray[9].ToString();
                lblDatePub.Text = dt2.Rows[0].ItemArray[10].ToString();

                SqlCommand cmdSelectEmp = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office FROM formEmployees INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE (EmployeeID = '" + SessionID() + "')", conn);
                SqlDataAdapter daSelectEmp = new SqlDataAdapter(cmdSelectEmp);
                DataTable dtSelectEmp = new DataTable("dtSelectEmp");
                daSelectEmp.Fill(dtSelectEmp);

                SqlCommand cmd31 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office FROM formEmployees INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE (EmployeeID = '" + SessionID() + "') AND (catDepartment.DepartmentName = '" + dtSelectEmp.Rows[0].ItemArray[3].ToString() + "') AND (catCollege.CollegeName = '" + dtSelectEmp.Rows[0].ItemArray[4].ToString() + "')", conn);
                SqlDataAdapter da31 = new SqlDataAdapter(cmd31);
                DataTable dt31 = new DataTable("dt31");
                da31.Fill(dt31);
                //notYetTested

                //HR Directors Office
                if (dt31.Rows[0].ItemArray[4].ToString() == "Adamson Office" && dt31.Rows[0].ItemArray[3].ToString() == "HR Directors Office" && dt31.Rows[0].ItemArray[1].ToString() == "Admin" && dt31.Rows[0].ItemArray[2].ToString() == "Director")
                {
                    ddlApprovalHR.Enabled = true;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalDean.Enabled = false;
                }
                //HR Salaries and Benefits
                else if (dt31.Rows[0].ItemArray[4].ToString() == "Adamson Office" && dt31.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits" && dt31.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dt31.Rows[0].ItemArray[2].ToString() == "Head")
                {
                    ddlApprovalHR.Enabled = true;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalDean.Enabled = false;
                }
                //error

                if (dt31.Rows[0].ItemArray[4].ToString() == ddlOffice.Text)
                {
                    //UserLevel
                    if (dt31.Rows[0].ItemArray[1].ToString() == "Faculty" || dt31.Rows[0].ItemArray[1].ToString() == "Employee")
                    {//Position
                        if (dt31.Rows[0].ItemArray[2].ToString() == "Dean" || dt31.Rows[0].ItemArray[2].ToString() == "Director")
                        {
                            ddlApprovalHR.Enabled = false;
                            ddlApprovalHead.Enabled = false;
                            ddlApprovalDean.Enabled = true;
                        }
                    }
                
                 //APPROVAL 2
                    //UserLevel
                    else if (dt31.Rows[0].ItemArray[1].ToString() == "Faculty" || dt31.Rows[0].ItemArray[1].ToString() == "Employee")
                    {//Position
                        if (dt31.Rows[0].ItemArray[2].ToString() == "Chairperson" || dt31.Rows[0].ItemArray[2].ToString() == "Head" || dt31.Rows[0].ItemArray[2].ToString() == "Supervisor")
                        {
                            ddlApprovalHR.Enabled = false;
                            ddlApprovalHead.Enabled = true;
                            ddlApprovalDean.Enabled = false;
                        }
                    }
                    else
                    {

                    }
                }
            

                //Approval Enable Dept Head
                if (dt2.Rows[0].ItemArray[12].ToString() != "Denied" && dt2.Rows[0].ItemArray[12].ToString() != "Approved")
                {
                    ddlApprovalHead.Visible = true;
                    ddlApprovalDean.Visible = true;
                    ddlApprovalHR.Visible = true;
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHead.Enabled = true;
                    ddlApprovalHR.Enabled = false;
                }
                else if (dt2.Rows[0].ItemArray[13].ToString() != "Denied" && dt2.Rows[0].ItemArray[13].ToString() != "Approved")
                {
                    ddlApprovalHead.Visible = true;
                    ddlApprovalDean.Visible = true;
                    ddlApprovalHR.Visible = true;
                    ddlApprovalDean.Enabled = true;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalHR.Enabled = false;
                }
                else if (dt2.Rows[0].ItemArray[14].ToString() != "Denied" && dt2.Rows[0].ItemArray[14].ToString() != "Approved")
                {
                    ddlApprovalHead.Visible = true;
                    ddlApprovalDean.Visible = true;
                    ddlApprovalHR.Visible = true;
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalHR.Enabled = true;
                }
                else
                {
                    ddlApprovalHead.Visible = true;
                    ddlApprovalDean.Visible = true;
                    ddlApprovalHR.Visible = true;
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalHR.Enabled = false;
                }

            }
        }
        public void Edit()
        {
            string FlexiID = Request.QueryString["FlexiID"];
            SqlCommand cmd = new SqlCommand("UPDATE formFlexi SET  HeadApproval ='" + ddlApprovalHead.Text + "',DeanApproval ='" + ddlApprovalDean.Text + "', HRApproval ='" + ddlApprovalHR.Text + "' WHERE(LeaveID = " + FlexiID + ")", conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            conn.Open();
            cmd.Parameters.AddWithValue("HeadApproval", ddlApprovalHead.Text);
            cmd.Parameters.AddWithValue("DeanApproval", ddlApprovalDean.Text);
            cmd.Parameters.AddWithValue("HRApproval", ddlApprovalHR.Text);
            cmd.ExecuteNonQuery();
            conn.Close();
            lblConfirmationMessage.Visible = true;
            lblConfirmationMessage.Text = "You successfully put your approval. Thank You";


        }
        void notification()
        {
            string formCBAID = Request.QueryString["FlexiID"];
            string desc = "Your Application for Flexi Time has been \n"
                + ddlApprovalHead.SelectedItem.Text + " by the Dept. Head/Supervisor \n"
                + ddlApprovalDean.SelectedItem.Text + " by the Dean/Director \n"
                + ddlApprovalHR.SelectedItem.Text + " by the HR Director \n";

            string sqlc = "INSERT INTO formMessegeSender " +
                "(Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID, Readed) " +
                "VALUES ('Flexiform Request','" +
                desc + "','" +
                DateTime.Now + "'," +
                cbasender(formCBAID) +
                ",2," +
                SessionID() +
                ", 0)";
            SqlCommand cmd = new SqlCommand(sqlc, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        string cbasender(string src)
        {
            string sqlx = "SELECT EmployeeID FROM formFlexi WHERE FlexiID = " + src;
            SqlDataAdapter cmd = new SqlDataAdapter(sqlx, conn);
            DataTable tbl = new DataTable();

            conn.Open();
            cmd.Fill(tbl);
            conn.Close();

            return tbl.Rows[0][0].ToString();
        }

        void autoTime()
        {
            int a1 = Convert.ToInt16(ddlOhr.Text);
            int a2 = Convert.ToInt16(ddlOhr0.Text);
            int b1 = Convert.ToInt16(ddlOmin.Text);
            int b2 = Convert.ToInt16(ddlOmin0.Text);

            int tHour, tMin;
            string totalTime;

            tHour = a2 - a1;
            tMin = b2 - b1;
            totalTime = tHour + ":" + tMin;
            txtOtotal.Text = totalTime;

            int z1 = Convert.ToInt16(ddlFlexiHour.Text);
            int z2 = Convert.ToInt16(ddlFlexihr0.Text); 
            int y1 = Convert.ToInt16(ddlFlexiMin.Text);
            int y2 = Convert.ToInt16(ddlFleximin1.Text);
            int fthour, ftmin;
            string ftotaltime;

            fthour = z2 - z1;
            ftmin = y2 - y1;
            ftotaltime = fthour + ":" + ftmin;
            txtftotal.Text = ftotaltime;
        }

        protected void btnClear_Click(object sender, EventArgs e)
        {
            txtActivityReason.Text = null;
            txtEmpName.Text = null;
            txtEmpNo.Text = null;
            txtfDate.Text = null;
            txtfTime.Text = null;
            txtftotal.Text = null;
            txtODate.Text = null;
            txtOTime.Text = null;
            txtOtotal.Text = null;
        }

    }
}